﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using App.Core.DB;
using App.Core.Extensions;
using System.Net;
using System.IO;
using CarlosAg.ExcelXmlWriter;
using System.Data;
using MsExcel = Microsoft.Office.Interop.Excel;
using Excel;

namespace WebScrapper.Implementations
{
    public abstract class BaseImplementation<T> where T : App.Core.Base.Model.BaseEntity
    {        
        protected System.Windows.Forms.RichTextBox StatusTextBox { get; set; }
        
        protected BaseImplementation(System.Windows.Forms.RichTextBox txtStatusTextBox)
        {
            this.StatusTextBox = txtStatusTextBox;
        }
        private delegate void RichTextBoxUpdateEventHandler(string message);
        /// <summary>
        /// Upadtes Status Text in the Text area
        /// </summary>
        /// <param name="message"></param>
        protected void UpdateStatusText(String message)
        {
            if (StatusTextBox.InvokeRequired)
                StatusTextBox.Invoke(new RichTextBoxUpdateEventHandler(UpdateStatusText), new[] { message });
            else
            {
                StatusTextBox.AppendText(String.Format("{0}{1}", message, Environment.NewLine));
                StatusTextBox.SelectionStart = StatusTextBox.Text.Length;
                StatusTextBox.ScrollToCaret();
            }
        }
        protected String GetAuthenticatedPageContent(String url, String authenticationCookieData)
        {
            HttpWebRequest request = HttpWebRequest.Create(url) as HttpWebRequest;
            //set the user agent so it looks like IE to not raise suspicion 
            request.UserAgent = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)";
            request.Method = "GET";
            //set the cookie in the request header
            request.Headers.Add("Cookie", authenticationCookieData);

            //get the response from the server
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            string pageHtml = string.Empty;
            using (Stream stream = response.GetResponseStream())
            {
                using (StreamReader reader = new StreamReader(stream))
                {
                    pageHtml = reader.ReadToEnd();                    
                }
            }
            response.Close();
            return pageHtml;
        }
        private String[] _ReplacableChars = new String[] { "\t", "\r", "\n" };
        protected String CleanUpText(String text)
        {
            return text.IsNullOrEmpty() ? String.Empty : text.Replace("\r\n", String.Empty).StripExtraSpace(); //text.RemoveCharacters(_ReplacableChars);
        }
        public abstract void StartScrapping(String category);
 
        public abstract String OutputFile(List<T> collection, bool addColumnHeader, String outputFileName);

        #region DataTable to Excel
        protected String DataTableToExcel(System.Data.DataTable dt, IList<int?> ignoredFieldIndexes, bool addColumnHeader, String outputFileName)
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets.Add("Sheet1");
            if (dt != null && dt.Rows.Count > 0)
            {
                if (addColumnHeader)
                {
                    WorksheetRow headerRow = sheet.Table.Rows.Add();
                    addHeaderRow(headerRow, dt, ignoredFieldIndexes);
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    WorksheetRow row = sheet.Table.Rows.Add();
                    System.Data.DataRow dtRow = dt.Rows[i];
                    addDataRow(row, dtRow, ignoredFieldIndexes);
                }
            }

            //row.Cells.Add("Hello World");                    
            String filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "OutputExcel");
            if (!System.IO.Directory.Exists(filePath))
                System.IO.Directory.CreateDirectory(filePath);

            filePath = System.IO.Path.Combine(filePath, outputFileName);

            book.Save(filePath);

            return filePath;
        }

        private void addHeaderRow(WorksheetRow row, System.Data.DataTable dt, IList<int?> ignoredFieldIndexes)
        {
            //foreach (System.Data.DataColumn column in dt.Columns)
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (ignoredFieldIndexes == null || ignoredFieldIndexes.Count == 0 || ignoredFieldIndexes.SingleOrDefault(P => P == i) == null)
                    row.Cells.Add(dt.Columns[i].ColumnName);
            }
        }
        private void addDataRow(WorksheetRow row, System.Data.DataRow dtRow, IList<int?> ignoredFieldIndexes)
        {
            for (int i = 0; i < dtRow.ItemArray.Length; i++)// object data in dtRow.ItemArray)
            {
                if (ignoredFieldIndexes == null || ignoredFieldIndexes.Count == 0 || ignoredFieldIndexes.SingleOrDefault(P => P == i) == null)
                    row.Cells.Add(ConvertToString(dtRow[i]));                
            }
        }
        private String ConvertToString(object data)
        {
            if (data.GetType() == typeof(String))
                return NullHandler.GetString(data);
            else if (data.GetType() == typeof(DateTime))
                return NullHandler.GetDateTime(data).ToString("MM/dd/yyyy");
            else if (data.GetType() == typeof(decimal))
                return String.Format("{0:0,0.00}", NullHandler.GetDecimal(data));
            else
                return NullHandler.GetString(data);
        }
        #endregion


        #region DataTable to Excel With Com Object
        protected String DataTableToExcel(DataTable dt, bool addColumnHeader, String outputFileName)
        {
            MsExcel.Application xlApp;
            MsExcel.Workbook xlWorkBook;
            MsExcel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new MsExcel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (MsExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            if (addColumnHeader)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                }
            }            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    xlWorkSheet.Cells[i + 2, j + 1] = ConvertToString(dt.Rows[i][j]);
                }
            }
            ///Save the File
            String filePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "OutputExcel");
            if (!System.IO.Directory.Exists(filePath))
                System.IO.Directory.CreateDirectory(filePath);

            filePath = System.IO.Path.Combine(filePath, outputFileName);

            xlWorkBook.SaveAs(filePath, MsExcel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, MsExcel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            return filePath;
            //MessageBox.Show("Excel file created to " + DestFolder);            
        }

        protected void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                UpdateStatusText(String.Format("Error While Releasing COM Object for Excel Creation. Error: {0}{1}", Environment.NewLine, ex.Message));                
            }
            finally
            {
                GC.Collect();
            }
        }
        #endregion DataTable to Excel With Com Object

        #region Read Excel
        public DataTable ReadExcel(String filePath)
        {
            IExcelDataReader excelReader = null;
            DataTable result = new DataTable("ExcelData");
            try
            {
                FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
                if (String.Compare(Path.GetExtension(filePath), ".xls", true) == 0)
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                else if (String.Compare(Path.GetExtension(filePath), ".xlsx", true) == 0)
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

                if (excelReader != null)
                {
                    excelReader.IsFirstRowAsColumnNames = true;
                    result = excelReader.AsDataSet().Tables[0];
                }
            }
            catch (Exception ex)
            {
                //log.Error(String.Format("Error Reading Excel File. Name: {0}. Error Message: {1} ", filePath, ex.Message));
            }
            finally
            {
                if (excelReader != null && !excelReader.IsClosed)
                    excelReader.Close();
            }
            return result;
        }
        #endregion

    }
}
